package com.sduept.nwld.dataserver.manager.fault.tripping;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.persistence.AttributeConverter;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author     Author
 * @version    V1.0
 * @Date       2018年8月7日 上午8:35:55
 */
@Service
public class EmsOracleDAO {

    @Resource(name = "emsOracleDataSource")
	protected DataSource emsOracleDataSource;


	public List<Object[]> query(String sql) {
		return nativeQuery(sql, null);
	}

	public List<Object[]> nativeQuery(String sql, Object[] whereArgs) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<Object[]> result = new ArrayList<>();
		try {
			conn = emsOracleDataSource.getConnection();
			pst = conn.prepareStatement(sql);
			if (whereArgs != null && whereArgs.length > 0) {
				for (int i = 0; i < whereArgs.length; i++) {
					pst.setObject(i + 1, whereArgs[i]);
				}
			}
			rs = pst.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			while (rs.next()) {
				Object[] obj = new Object[meta.getColumnCount()];
				for (int i = 0; i < obj.length; i++) {
					obj[i] = rs.getObject(i + 1);
				}
				result.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			try {
				pst.close();
			} catch (SQLException e) {
			}
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}

	/**
	 * 执行本地sql查询，返回list
	 *
	 * @param <T>
	 * @param sql       name=? and id=? or other=?
	 * @param whereArgs 长度要与whereClause的问号对齐
	 * @return
	 */
	public <T> List<T> nativeJPAQuery(Class<T> cla, String sql, Object[] whereArgs) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(emsOracleDataSource);
		List<T> result = jdbcTemplate.query(sql, whereArgs, new BeanPropertyRowMapper<T>(cla));
		return result;
//      return	this.nativeQuery(cla, sql, whereArgs);
	}

	/**
	 * 本地sql查询，讲数据库数据映射到T的实例中，如果数据库列名与对象属性不一致,使用@column（name）进行标注
	 * 注：如果数据库字段类型与实体字段类型不一致 ，会导致类型转换发生异常
	 *
	 * @param cla
	 * @param nativeSql
	 * @param args
	 * @return
	 */
	public <T> List<T> nativeQuery(Class<T> cla, String nativeSql, Object[] args) {
		List<T> result = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<String> allColumns = new ArrayList<>();
		try {
			conn = emsOracleDataSource.getConnection();
			pst = conn.prepareStatement(nativeSql);
			if (args != null) {
				for (int i = 0; i < args.length; i++) {
					pst.setObject(i + 1, args[i]);
				}
			}
			rs = pst.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			for (int i = 0; i < meta.getColumnCount(); i++) {
				allColumns.add(meta.getColumnLabel(i + 1));// 从1开始
			}
			List<Field> fieldList = getClassDeclaredFields(cla);
			while (rs.next()) {
				T t = cla.newInstance();
				for (Field field : fieldList) {
					field.setAccessible(true);
					Column fieldColumnMap = field.getAnnotation(Column.class);
					if (fieldColumnMap == null || StringUtils.isEmpty(fieldColumnMap.name())) {
						if (allColumns.contains(field.getName())) {
							if (field.getType().isEnum()) {
								Object value = rs.getObject(field.getName());
								Object enumValue = getEnumValue(field, value);
								field.set(t, enumValue);
							} else {
								Object value = rs.getObject(field.getName());
								setField(t,field,value);
							}
						}
					} else {
						String columnName = fieldColumnMap.name();
						if (allColumns.contains(columnName)) {
							if (field.getType().isEnum()) {
								Object value = rs.getObject(columnName);
								Object enumValue = getEnumValue(field, value);
								field.set(t, enumValue);
							} else {
								Object value = rs.getObject(columnName);
								setField(t,field,value);
							}
						}
					}
				}
				result.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private <T> void setField(T t,Field field,Object value) throws IllegalArgumentException, IllegalAccessException {
		if(value != null) {
			if(field.getType() == String.class) {
				field.set(t, value.toString());
			} else if(field.getType() == Long.class || field.getType() == long.class) {
				field.set(t, getLongValue(value));
			} else if(field.getType() == int.class || field.getType() == Integer.class ) {
				field.set(t, getIntValue(value));
			} else if(field.getType() == java.util.Date.class) {
				field.set(t, new Date(((Timestamp)value).getTime()));
			}
		}
	}
	private long getLongValue(Object value) {
		if(value instanceof Long) {
			return ((Long)value).longValue();
		} else if(value instanceof Integer) {
			return ((Integer)value).intValue();
		} else if(value instanceof BigDecimal) {
			return  ((BigDecimal)value).longValue();
		} else if(value instanceof Date) {
			return  ((Date)value).getTime();
		} else if(value instanceof Timestamp) {
			return ((Timestamp)value).getTime();
		} else if(value instanceof Short) {
			return ((Short)value).intValue();
		} else {
			return Long.valueOf(value.toString()) ;
		}
	}
	private int getIntValue(Object value) {
		if(value instanceof Long) {
			return ((Long)value).intValue();
		} else if(value instanceof Integer) {
			return ((Integer)value).intValue();
		} else if(value instanceof BigDecimal) {
			return  ((BigDecimal)value).intValue();
		} else if(value instanceof Short) {
			return ((Short)value).intValue();
		} else {
			return Integer.valueOf(value.toString()) ;
		}
	}

	@SuppressWarnings({"rawtypes", "unchecked"})
	private Object getEnumValue(Field field, Object value) {
		Object reuslt = null;
		Convert convertAnnotation = field.getAnnotation(Convert.class);
		boolean assignableFrom = AttributeConverter.class.isAssignableFrom(convertAnnotation.converter());
		// 如果是接口的实现
		if (assignableFrom) {
			AttributeConverter convert = null;
			try {
				convert = (AttributeConverter) convertAnnotation.converter().newInstance();
			} catch (InstantiationException | IllegalAccessException e) {
				e.printStackTrace();
			}
			// Class<?> type = field.getType();
			reuslt = convert.convertToEntityAttribute(value);
		}
		return reuslt;
	}

	private <T> List<Field> getClassDeclaredFields(Class<? super T> clazz) {
		List<Field> result = new ArrayList<Field>();
		for (; clazz != Object.class; clazz = clazz.getSuperclass()) {
			try {
				Field[] fields = clazz.getDeclaredFields();
				for (Field f : fields) {
					result.add(f);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}


}
